﻿Public Class MedicamentoDAO
    Public Shared TROQUEL As Integer = 1
    Public Shared CODIGO_BARRA As Integer = 2
    Public Shared NOMBRE As Integer = 3


    Public Shared Function insertarMedicamento(ByVal MedicamentoTroquel As Integer, ByVal MedicamentoCodigoBarra As String, ByVal MedicamentoNombre As String, ByVal MedicamentoPresentacion As String, ByVal MedicamentoImporte As Decimal, ByVal LaboratorioId As Integer, ByVal TamanoMedicamentoId As Integer, ByVal AccionFarmacologicaId As Integer, ByVal MonodrogaId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            Constante.esNulo(MedicamentoCodigoBarra)
            Constante.esNulo(MedicamentoNombre)
            Constante.esNulo(MedicamentoPresentacion)
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_Medicamento"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.INSERTAR).DbType = DbType.Int32
                    .AddWithValue("@MedicamentoId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@MedicamentoTroquel", MedicamentoTroquel).DbType = DbType.Int32
                    .AddWithValue("@MedicamentoCodigoBarra", MedicamentoCodigoBarra).DbType = DbType.String
                    .AddWithValue("@MedicamentoNombre", MedicamentoNombre).DbType = DbType.String
                    .AddWithValue("@MedicamentoPresentacion", MedicamentoPresentacion).DbType = DbType.String
                    .AddWithValue("@MedicamentoImporte", MedicamentoImporte).DbType = DbType.Double
                    .AddWithValue("@LaboratorioId", LaboratorioId).DbType = DbType.Int32
                    .AddWithValue("@TamanoMedicamentoId", TamanoMedicamentoId).DbType = DbType.Int32
                    .AddWithValue("@AccionFarmacologicaId", AccionFarmacologicaId).DbType = DbType.Int32
                    .AddWithValue("@MonodrogaId", MonodrogaId).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerMedicamentos(ByVal MedicamentoId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_Medicamento_obtener", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@MedicamentoId", MedicamentoId).DbType = DbType.Int32
            da.Fill(ds, "Medicamentos")

            Return ds.Tables("Medicamentos")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function actualizarMedicamento(ByVal MedicamentoId As Integer, ByVal MedicamentoTroquel As Integer, ByVal MedicamentoCodigoBarra As String, ByVal MedicamentoNombre As String, ByVal MedicamentoPresentacion As String, ByVal MedicamentoImporte As Decimal, ByVal LaboratorioId As Integer, ByVal TamanoMedicamentoId As Integer, ByVal AccionFarmacologicaId As Integer, ByVal MonodrogaId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            Constante.esNulo(MedicamentoCodigoBarra)
            Constante.esNulo(MedicamentoNombre)
            Constante.esNulo(MedicamentoPresentacion)
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_Medicamento"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.ACTUALIZAR).DbType = DbType.Int32
                    .AddWithValue("@MedicamentoId", MedicamentoId).DbType = DbType.Int32
                    .AddWithValue("@MedicamentoTroquel", MedicamentoTroquel).DbType = DbType.Int32
                    .AddWithValue("@MedicamentoCodigoBarra", MedicamentoCodigoBarra).DbType = DbType.String
                    .AddWithValue("@MedicamentoNombre", MedicamentoNombre).DbType = DbType.String
                    .AddWithValue("@MedicamentoPresentacion", MedicamentoPresentacion).DbType = DbType.String
                    .AddWithValue("@MedicamentoImporte", MedicamentoImporte).DbType = DbType.Double
                    .AddWithValue("@LaboratorioId", LaboratorioId).DbType = DbType.Int32
                    .AddWithValue("@TamanoMedicamentoId", TamanoMedicamentoId).DbType = DbType.Int32
                    .AddWithValue("@AccionFarmacologicaId", AccionFarmacologicaId).DbType = DbType.Int32
                    .AddWithValue("@MonodrogaId", MonodrogaId).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()

            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function eliminarMedicamento(ByVal MedicamentoId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_Medicamento"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.ELIMINAR).DbType = DbType.Int32
                    .AddWithValue("@MedicamentoId", MedicamentoId).DbType = DbType.Int32
                    .AddWithValue("@MedicamentoTroquel", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@MedicamentoCodigoBarra", Constante.MENOSUNO).DbType = DbType.String
                    .AddWithValue("@MedicamentoNombre", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@MedicamentoPresentacion", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@MedicamentoImporte", Constante.MENOSUNO).DbType = DbType.Double
                    .AddWithValue("@LaboratorioId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@TamanoMedicamentoId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@AccionFarmacologicaId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@MonodrogaId", Constante.MENOSUNO).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With

            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function buscarMedicamento(ByVal TipoBusqueda As Integer, ByVal MedicamentoTroquel As Integer, ByVal MedicamentoCodigoBarra As String, ByVal MedicamentoNombre As String) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            Constante.esNulo(MedicamentoCodigoBarra)
            Constante.esNulo(MedicamentoNombre)
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_Medicamento_buscar", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@TipoBusqueda", TipoBusqueda).DbType = DbType.Int32
            If TipoBusqueda = TROQUEL Then
                da.SelectCommand.Parameters.AddWithValue("@MedicamentoTroquel", MedicamentoTroquel).DbType = DbType.Int32
            End If

            If TipoBusqueda = CODIGO_BARRA Then
                da.SelectCommand.Parameters.AddWithValue("@MedicamentoCodigoBarra", MedicamentoCodigoBarra).DbType = DbType.String
            End If

            If TipoBusqueda = NOMBRE Then
                da.SelectCommand.Parameters.AddWithValue("@MedicamentoNombre", MedicamentoNombre).DbType = DbType.String
            End If

            da.Fill(ds, "Medicamentos")

            Return ds.Tables("Medicamentos")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function idMedicamento(ByVal MedicamentoTroquel As Integer, ByVal MedicamentoCodigoBarra As String) As Integer
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            Constante.esNulo(MedicamentoCodigoBarra)
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_medicamento_id"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@MedicamentoTroquel", MedicamentoTroquel).DbType = DbType.Int32
                    .AddWithValue("@MedicamentoCodigoBarra", MedicamentoCodigoBarra).DbType = DbType.String
                End With
                .Connection = cn
                Return .ExecuteScalar()
            End With

            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return -1
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function coberturaMedicamento(ByVal MedicamentoId As Integer, ByVal ObraSocialXPlanId As Integer, ByVal Fecha As DateTime) As Double
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_medicamento_cobertura"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@MedicamentoId", MedicamentoId).DbType = DbType.Int32
                    .AddWithValue("@ObraSocialXPlanId", ObraSocialXPlanId).DbType = DbType.Int32
                    .AddWithValue("@Fecha", Fecha).DbType = DbType.DateTime
                End With
                .Connection = cn
                Return .ExecuteScalar()
            End With

            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return -1
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function
End Class
